package net.bolue.sysmng.db;

import java.io.StringReader;
import java.lang.reflect.Method;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SubSelect;

/**
 * 查询语句工具
 * @author Administrator
 *
 */
public class SelectSqlOpUtil {

	static CCJSqlParserManager parserManager = new CCJSqlParserManager();
	
	/**
	 * 根据查询语句和数据生成查询语句类
	 * @param sqlTp
	 * @param dataMap
	 * @return
	 * @throws JSQLParserException
	 */
	public static SelectSqlOp parseSelectSql(String sqlTp,Map<String,Object> dataMap) throws JSQLParserException{
		if(sqlTp==null||"".equals(sqlTp)){
			return null;
		}
		
		SelectSqlOp selectSqlOp = new SelectSqlOp();
		
		Select select = (Select) parserManager.parse(new StringReader(sqlTp));
		PlainSelect plainSelect = (PlainSelect)select.getSelectBody();
		List<SelectItem> selectItems = plainSelect.getSelectItems();
		String selectField = toStringSelect(selectItems);
		selectSqlOp.setFields(selectField);
		
		FromItem fromItem = plainSelect.getFromItem();
		selectSqlOp.setTable(fromItem.toString());
		
		List<Join> joins = plainSelect.getJoins();
		String join = toStringJoin(joins);
		selectSqlOp.setJoin(join);
		
		LinkedList<Object> paramterList = new LinkedList<Object>();
		Expression where = plainSelect.getWhere();
		String whereStr = toStringWhere(where,dataMap,paramterList);
		selectSqlOp.setWhere(whereStr);
		selectSqlOp.setParamterList(paramterList);
		
		List<OrderByElement> orderByEls = plainSelect.getOrderByElements();
		String orderByStr = toOrderByStr(orderByEls);
		selectSqlOp.setOrderBy(orderByStr);
		
		return selectSqlOp;
	}
	
	/**
	 * 将sql语句里select部分转化select字符串
	 * @param selectItems
	 * @return
	 */
	private static String toStringSelect(List<SelectItem> selectItems){
		StringBuffer sb = new StringBuffer();
		if(selectItems!=null&&selectItems.size()>0){
			for(int i=0;i<selectItems.size();i++){
				SelectItem selectItem = selectItems.get(i);
				if(i==0){
					sb.append(selectItem);
				}else{
					sb.append(","+selectItem);
				}
			}
		}
		return sb.toString();
	}
	
	private static String toStringJoin(List<Join> joins){
		StringBuffer sb = new StringBuffer();
		if(joins!=null&&joins.size()>0){
			for(Join join : joins){
				if(join.isSimple()){
					sb.append(","+join);
				}else{
					sb.append(" "+join);
				}
			}
		}
		return sb.toString();
	}
	
	private static String toStringWhere(Expression where,Map<String,Object> whereMap,LinkedList<Object> paramterList){
		StringBuffer sb = new StringBuffer();
		Object columnObj = invokeMethod(where, "getLeftExpression");
		if(columnObj instanceof LongValue){//如果解析后是 1=1
			return where.toString();
		}
		
		if(where instanceof OrExpression||where instanceof AndExpression){
			BinaryExpression be = (BinaryExpression)where;
			Expression leftExpression = be.getLeftExpression();
			Expression rightExpression = be.getRightExpression();
			String left = toStringWhere(leftExpression,whereMap,paramterList);
			String logic = be.getStringExpression();
			String right = toStringWhere(rightExpression,whereMap,paramterList);
			sb.append(left);
			if(!"".equals(left)&&!"".equals(right)){
				sb.append(" "+logic);
			}
			if(!"".equals(right)){
				sb.append(" "+right);
			}
		}else if(where instanceof BinaryExpression){
			BinaryExpression be = (BinaryExpression)where;
			Expression rightExpression = be.getRightExpression();
			String express = be.getStringExpression();
			Expression leftExpression = be.getLeftExpression();
			String left = toStringWhere(leftExpression,whereMap,paramterList);
			String right = toStringWhere(rightExpression,whereMap,paramterList);
			
			if("?".equals(right)){
				if(whereMap.get(left)!=null){
					sb.append(where);
					if(paramterList==null){
						paramterList = new LinkedList<Object>();
					}
					if("LIKE".equals(express)){
						paramterList.add("%"+whereMap.get(left)+"%");
					}else{
						paramterList.add(whereMap.get(left));
					}
				}
			}else if("".equals(right)){
				
			}else {
				sb.append(where);
			}
		}else if(where instanceof InExpression){
			InExpression be = (InExpression)where;
			Expression leftExpression = be.getLeftExpression();
			ItemsList il = be.getRightItemsList();
			if("(?)".equals(il.toString())){
				if(whereMap.get(leftExpression.toString())!=null){
					sb.append(where);
					if(paramterList==null){
						paramterList = new LinkedList<Object>();
					}
					paramterList.add(whereMap.get(leftExpression.toString()));
				}
			}else{
				sb.append(where);
			}
		}else if(where instanceof SubSelect){
			SubSelect be = (SubSelect)where;
			PlainSelect plainSelect = (PlainSelect)be.getSelectBody();
			Expression subWhere = plainSelect.getWhere();
			String subWhereStr = toStringWhere(subWhere,whereMap,paramterList);
			if(!"".equals(subWhereStr)){
				sb.append(where);
			}
		}else{
			sb.append(where);
		}
		return sb.toString();
	}
	
	private static String toOrderByStr(List<OrderByElement> orderByEls){
		StringBuffer sb = new StringBuffer();
		if(orderByEls!=null&&orderByEls.size()>0){
			for(int i=0;i<orderByEls.size();i++){
				OrderByElement orderByEl = orderByEls.get(i);
				if(i>0){
					sb.append(",");
				}
				sb.append(orderByEl);
			}
		}
		return sb.toString();
	}
	
	private static Object invokeMethod(Object obj, String methodFunc){
		try {
			Method method = obj.getClass().getMethod(methodFunc, null);
			return method.invoke(obj, null);
		} catch (Exception e) {
			return null;
		}
	}
}
